package com.email.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.rowset.CachedRowSet;

import com.email.base.DBConnection;
import com.sun.rowset.CachedRowSetImpl;
/**
 * 文件夹操作类
 * @author victor
 *
 */
public class FolderDao {
	private static FolderDao instance;

	private FolderDao() {
	}

	/**
	 * 获取FolderDao类的实例
	 * @return
	 */
	public static FolderDao getInstance() {
		if (instance == null) {
			instance = new FolderDao();
		}
		return instance;
	}

	/**
	 * 添加新文件夹
	 * @param folder
	 * @return
	 */
	public boolean addFolder(FolderBean folder){
		boolean flag = false;
		String sql = "INSERT INTO folder(FOLDER_NAME,FOLDER_TYPE,PARENT_ID,USERNAME) VALUES('"+folder.getFolder_name()+"','"+folder.getFolder_type()+"','"+folder.getParent_id()+"','"+folder.getUsername()+"')";
		Connection connect = DBConnection.getConnection();
		Statement stmt = null;
		try {
			stmt = connect.createStatement();
			flag = stmt.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	/**
	 * 获取当前用户的所有文件夹信息
	 * @param username
	 * @return
	 */
	public ArrayList<FolderBean> getFolderList(String username) {
		ArrayList<FolderBean> folderList = new ArrayList<FolderBean>();
		String sql = "select ID,USERNAME,PARENT_ID,FOLDER_NAME,FOLDER_TYPE from folder where USERNAME= '"+username+"'";
		Connection connect = DBConnection.getConnection();
		Statement stmt = null;
		try {
			stmt = connect.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				FolderBean folder = new FolderBean();
				folder.setId(rs.getInt("ID"));
				folder.setUsername(rs.getString("USERNAME"));
				folder.setFolder_name(rs.getString("FOLDER_NAME"));
				folder.setParent_id(rs.getInt("PARENT_ID"));
				folder.setId(rs.getInt("FOLDER_TYPE"));
				folderList.add(folder);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return folderList;
	}
	
	/**
	 * 获取当前用户的所有顶级文件夹信息（folder.PARENT_ID=0）
	 * @param username
	 * @return
	 */
	public ArrayList<FolderBean> getTopFolderList(String username){
		ArrayList<FolderBean> folderList = new ArrayList<FolderBean>();
		String sql = "select ID,USERNAME,PARENT_ID,FOLDER_NAME,FOLDER_TYPE from folder where USERNAME= '"+username+"' and PARENT_ID = -1 ";
		Connection connect = DBConnection.getConnection();
		Statement stmt = null;
		try {
			stmt = connect.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				FolderBean folder = new FolderBean();
				folder.setId(rs.getInt("ID"));
				folder.setUsername(rs.getString("USERNAME"));
				folder.setFolder_name(rs.getString("FOLDER_NAME"));
				folder.setParent_id(rs.getInt("PARENT_ID"));
				folder.setId(rs.getInt("FOLDER_TYPE"));
				folderList.add(folder);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return folderList;
	}

	/**
	 * 获取当前用户下指定ID的所有文件夹信息，这里是递归调用。
	 * @param parent_id
	 * @param username
	 * @return
	 * @throws SQLException 
	 */
	public List<FolderBean> getAllArray(Integer parent_id, String username) throws SQLException{
		List<FolderBean> folderList = null;
		String sql = "select ID,USERNAME,PARENT_ID,FOLDER_NAME,FOLDER_TYPE from folder where USERNAME= '"+username+"' and PARENT_ID = "+parent_id+" ";
		Connection connect = DBConnection.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		CachedRowSet cs =null;
		try {
		    cs = new CachedRowSetImpl();
			stmt = connect.createStatement();
			rs = stmt.executeQuery(sql);
		    cs.populate(rs); 		
			folderList = new ArrayList<FolderBean>();
			while(cs.next()){
				FolderBean folder = new FolderBean();
				folder.setId(cs.getInt("ID"));
				folder.setUsername(cs.getString("USERNAME"));
				folder.setFolder_name(cs.getString("FOLDER_NAME"));
				folder.setParent_id(cs.getInt("PARENT_ID"));
				folder.setFolder_type(cs.getInt("FOLDER_TYPE"));
				folder.setChildren(getChildrenArray(folder.getId(), username));
				folderList.add(folder);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	    cs.close();
	    stmt.close();
	    connect.close(); 
	    return folderList;
	}
	
	/**
	 * 获取当前用户下指定parent_id文件夹信息，这里是递归调用。
	 * @param parent_id
	 * @param username
	 * @return
	 * @throws SQLException 
	 */
	public List<FolderBean> getChildrenArray(Integer parent_id, String username) throws SQLException{
		ArrayList<FolderBean> folderList = null;
		String sql = "select ID,USERNAME,PARENT_ID,FOLDER_NAME,FOLDER_TYPE from folder where USERNAME= '"+username+"' and PARENT_ID = "+parent_id+" ";
		Connection connect = DBConnection.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		CachedRowSet cs = null;
		try {
			stmt = connect.createStatement();
			rs = stmt.executeQuery(sql);
		    cs = new CachedRowSetImpl();
			folderList = new ArrayList<FolderBean>();
		    cs.populate(rs); 
			while(cs.next()){
				FolderBean folder = new FolderBean();
				folder.setId(cs.getInt("ID"));
				folder.setUsername(cs.getString("USERNAME"));
				folder.setFolder_name(cs.getString("FOLDER_NAME"));
				folder.setParent_id(cs.getInt("PARENT_ID"));
				folder.setFolder_type(cs.getInt("FOLDER_TYPE"));
				folder.setChildren(getChildrenArray(folder.getId(), username));
				folderList.add(folder);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	    cs.close();
	    stmt.close();
	    connect.close();
		return folderList;
	}
	
	public static void main(String[] args){
		List<FolderBean> folderArray = null;
		try {
			folderArray = FolderDao.getInstance().getAllArray(-1, "test");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(folderArray.get(0).getChildren().get(0).toString());
	}
}
